﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace SSCCMembership.Web.Services.Reporting
{
    [ErrorHandlerBehavior]
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class DirectDBAccess
    {

        private string ConnectionString { get { return System.Configuration.ConfigurationManager.ConnectionStrings["mainConn"].ConnectionString; } }
        [OperationContract]
        public DBTable ExecuteQuery(string QueryString)
        {

            return ExecuteQuery(QueryString, new SqlParameter[0]);
        }
        public void SaveQuery(string QueryName, string Query, KeyValuePair<string, System.Data.SqlDbType> Parameters)
        {

        }

        public DBTable ExecuteQuery(string QueryString, SqlParameter[] Parameters)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    foreach (var Param in Parameters)
                    {
                        command.Parameters.Add(Param);
                    }
                    connection.Open();
                    SqlDataReader Reader = command.ExecuteReader();
                    List<string> Headings = new List<string>();
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        Headings.Add(Reader.GetName(i));
                    }
                    List<DBRow> Rows = new List<DBRow>();
                    while (Reader.Read())
                    {
                        List<object> Fields = new List<object>();
                        for (int i = 0; i < Reader.FieldCount; i++)
                        {
                            object v;
                            if (Reader[i].GetType() == typeof(System.DBNull))
                                v = null;
                            else
                                v = Reader[i];

                            Fields.Add(v);
                        }
                        Rows.Add(new DBRow() { Values = Fields.ToArray() });
                    }

                    var DBTable = new DBTable() { Headers = Headings.ToArray(), Rows = Rows.ToArray() };
                    SaveAutoComplete(QueryString);
                    return DBTable;
                }
            }
            catch (Exception ex)
            {
                return new DBTable()
                {
                    Headers = new string[] { "Error" },
                    Rows = new DBRow[] { new DBRow() { Values = new string[] { ex.GetBaseException().Message } } }
                };
            }

        }
        void SaveAutoComplete(string QueryString)
        {
            //DataModel DC = new DataModel();
            //if (!DC.SSCC_REF_DBQueryAutoComplete.Where((A)=>A.Value == QueryString).Any())
            //{
            //    DC.AddToSSCC_REF_DBQueryAutoComplete(new SSCC_REF_DBQueryAutoComplete() { Value = QueryString });
            //    DC.SaveChanges();
            //}
        }

        [OperationContract]
        public string ExecuteStatement(string Statement)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(Statement, connection);
                    connection.Open();
                    int i = command.ExecuteNonQuery();
                    return String.Format("{0}{1}{2} rows were affected.", Statement, Environment.NewLine, i);
                }
            }
            catch (Exception ex)
            {
                return String.Format("{0}{1}{2} rows were affected.", Statement, Environment.NewLine, ex.GetBaseException().Message);
            }
        }

        [OperationContract]
        public string[] LoadAutoComplete()
        {
            return new string[0];
            //DataModel DC = new DataModel();
            //return DC.SSCC_REF_DBQueryAutoComplete.Select((AC) => AC.Value).ToArray();
        }

        // Add more operations here and mark them with [OperationContract]
    }

    [DataContract]
    public class DBTable
    {
        [DataMember]
        public string[] Headers { get; set; }
        [DataMember]
        public DBRow[] Rows { get; set; }
    }
    public class DBRow
    {
        [DataMember]
        public object[] Values { get; set; }
    }
}
